import pymysql
from pymysql import cursors

class Database:
    def __init__(self, host='127.0.0.1', port=3306, user='root', password='123456', db_name='happy_match'):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db_name = db_name
        self.connection = None
        
    def connect(self):
        """建立与MySQL数据库的连接"""
        try:
            self.connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                charset='utf8mb4',
                cursorclass=cursors.DictCursor
            )
            print("数据库连接成功!")
            self._initialize_database()
            return True
        except Exception as e:
            print(f"数据库连接失败: {e}")
            return False
            
    def _initialize_database(self):
        """初始化数据库和表结构"""
        try:
            with self.connection.cursor() as cursor:
                # 创建数据库（如果不存在）
                cursor.execute(f"CREATE DATABASE IF NOT EXISTS {self.db_name}")
                cursor.execute(f"USE {self.db_name}")
                
                # 创建玩家表
                cursor.execute("""
                CREATE TABLE IF NOT EXISTS player (
                    player_id INT AUTO_INCREMENT PRIMARY KEY,
                    username VARCHAR(50) NOT NULL UNIQUE,
                    password VARCHAR(100) NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
                """)
                
                # 创建关卡表
                cursor.execute("""
                CREATE TABLE IF NOT EXISTS level (
                    level_id INT AUTO_INCREMENT PRIMARY KEY,
                    difficulty INT NOT NULL,
                    target_score INT NOT NULL,
                    time_limit INT NOT NULL,
                    layout TEXT
                )
                """)
                
                # 创建分数表
                cursor.execute("""
                CREATE TABLE IF NOT EXISTS score (
                    score_id INT AUTO_INCREMENT PRIMARY KEY,
                    player_id INT NOT NULL,
                    level_id INT NOT NULL,
                    score INT NOT NULL,
                    played_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (player_id) REFERENCES player(player_id),
                    FOREIGN KEY (level_id) REFERENCES level(level_id)
                )
                """)
                
                # 创建道具表
                cursor.execute("""
                CREATE TABLE IF NOT EXISTS item (
                    item_id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(50) NOT NULL,
                    description TEXT,
                    effect VARCHAR(100)
                )
                """)
                
                # 创建玩家道具关联表
                cursor.execute("""
                CREATE TABLE IF NOT EXISTS player_item (
                    player_id INT NOT NULL,
                    item_id INT NOT NULL,
                    quantity INT DEFAULT 0,
                    PRIMARY KEY (player_id, item_id),
                    FOREIGN KEY (player_id) REFERENCES player(player_id),
                    FOREIGN KEY (item_id) REFERENCES item(item_id)
                )
                """)
                
                # 初始化一些基础关卡数据
                cursor.execute("SELECT COUNT(*) as count FROM level")
                level_count = cursor.fetchone()['count']
                
                if level_count == 0:
                    print("初始化关卡数据...")
                    levels = [
                        (1, 1000, 60, '{"size": 8, "types": 5}'),
                        (2, 2000, 60, '{"size": 8, "types": 6}'),
                        (3, 3000, 45, '{"size": 9, "types": 6}')
                    ]
                    cursor.executemany(
                        "INSERT INTO level (difficulty, target_score, time_limit, layout) VALUES (%s, %s, %s, %s)",
                        levels
                    )
            
            self.connection.commit()
            print("数据库初始化完成!")
        except Exception as e:
            print(f"数据库初始化失败: {e}")
            self.connection.rollback()
    
    def close(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            
    def execute_query(self, query, params=None):
        """执行查询并返回结果"""
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(query, params or ())
                return cursor.fetchall()
        except Exception as e:
            print(f"查询执行失败: {e}")
            return []
            
    def execute_update(self, query, params=None):
        """执行更新操作并提交"""
        try:
            with self.connection.cursor() as cursor:
                result = cursor.execute(query, params or ())
                self.connection.commit()
                return result
        except Exception as e:
            print(f"更新执行失败: {e}")
            self.connection.rollback()
            return 0 